{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Defining Schema in SQLAlchemy Core\r\n",
    "# 在 SQLAlchemy Core 中定义数据库结构"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Creating tables 创建表格\r\n",
    "Tables in SQLAlchemy are represented as an instance of the Table class. The Table constructor function accepts table name, metadata and one or more columns as arguments. Here is an example:\r\n",
    "SQLAlchemy 中的表表示为 Table 类的实例。Table 构造函数接受表名、元数据和一个或多个列作为参数。下面是一个例子:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "from sqlalchemy import MetaData, Table, String, Column, Text, DateTime, Boolean\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "blog = Table('blog', metadata, \r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('post_title', String(200), nullable=False),\r\n",
    "    Column('post_slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),\r\n",
    "    Column('published', Boolean(),  default=False),\r\n",
    "    Column('created_on', DateTime(), default=datetime.now),\r\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    ")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Let's step through the code line by line:\r\n",
    "\r\n",
    "让我们一行一行地来看代码:\r\n",
    "\r\n",
    "In line 1, we are importing several classes from the sqlalchemy package, which we will use to define the table.\r\n",
    "\r\n",
    "在第1行中，我们从sqlalchemy 包中引入了一些类，我们将使用它来定义表.\r\n",
    "\r\n",
    "In line 2, we are importing datetime class from the datetime package.\r\n",
    "\r\n",
    "在第2行，我们引入了 datetime (用于获取当前时间做为字段默认值 datetime.now)\r\n",
    "\r\n",
    "In line 4, we are creating a MetaData object. The MetaData object holds all the information about the database and the tables it contains. We use MetaData instance to create or drop tables in the database.\r\n",
    "\r\n",
    "在第4行中，我们正在创建一个 MetaData 对象 MetaData对象保存有关数据库及其包含的表的所有信息. 我们使用 MetaData 实例在数据库中创建或删除表\r\n",
    "\r\n",
    "In lines 6-14, we are defining the table schema. The columns of a table are created using Column instance. The Column constructor function accepts name and type. We can also pass additional arguments to it to define constraints and SQL constructs. The following table lists some commonly used SQL constraints.\r\n",
    "\r\n",
    "在第6-14行中，我们使用 `Column` 实例 来创建了表的字段, `Column` 的构造函数接受名称和类型。我们还可以向它传递额外的参数来定义约束和 SQL 构造。下表列出了一些常用的 SQL 约束\r\n",
    "\r\n",
    "| 约束            | 描述                                                         |\r\n",
    "| :-------------- | :----------------------------------------------------------- |\r\n",
    "| `primary_key`   | 布尔参数，设置为`True`时，将该列标记为表的主键。要创建复合主键，请在每一列上设置 `primary_key = True`  |\r\n",
    "| `nullable`      | 布尔参数, 设置为`False` 时, 在创建列时将使用 `NOT NULL`(不容许为空)的约束。它的默认值是`True`. |\r\n",
    "| `default`       | 它指定在插入新行时未指定列的值时使用的默认值。它可以接受标量值或 Python 可调用的值 |\r\n",
    "| `onupdate`      | 如果在更新行时没有向列提供值，则指定列的默认值。就像 关键字参数，它可以接受标量值或 Python 可调用的值 |\r\n",
    "| `unique`        | 一个布尔参数，如果设置为`True `,需确保列中的值必须是唯一的            |\r\n",
    "| `index`         | 如果设置为`True ` 创建一个索引列。它的默认值是`False`.              |\r\n",
    "| `autoincrement` | 它增加了自动增长选项，其默认值为`auto`,这意味着每次添加新记录时，它都会自动增加主键。如果希望自动增加组合主键中涉及的所有列，请将此参数设置为`True`, 若要禁用自动增量计数器，请将其设置为`False`. |"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Column Types 列类型\r\n",
    "\r\n",
    "The type dictates what kind of data a column can take. SQLAlchemy provides an abstraction for a wide range of types. Broadly speaking there are three categories of types.\r\n",
    "\r\n",
    "类型指定列可以采用的数据类型。SQLAlchemy 为范围广泛的类型提供了一个抽象。一般来说有三种类型。\r\n",
    "\r\n",
    "Generic Types 通用类型 SQL Standard Types  SQL标准类型 Vendor Specific Types  供应商特定类型\r\n",
    "\r\n",
    "## Generic Types 通用类型\r\n",
    "\r\n",
    "The Generic types refer to the common types which major database backends support. When we use generic types, SQLAlchemy uses the best available type on the database backend while creating tables. For example, in the preceding snippet, we have defined published column as a Boolean. The Boolean is a generic type. If we run the code against PostgreSQL database then SQLAlchemy will use the boolean type provided by PostgreSQL. On the other hand, if we run the code against MySQL, then SQLAlchemy will use SMALLINT type because MySQL doesn't support Boolean type. In the Python code, however, Boolean generic type is represented using bool type (True or False).\r\n",
    "\r\n",
    "通用类型指的是主要数据库后端支持的常见类型。当我们使用通用类型时，SQLAlchemy 在创建表时使用数据库后端的最佳可用类型。例如，在前面的代码片段中，我们将已发布列定义为布尔值。布尔类型是一种通用类型。如果我们对 PostgreSQL 数据库运行代码，那么 SQLAlchemy 将使用 PostgreSQL 提供的布尔类型。另一方面，如果我们针对 MySQL 运行代码，则 SQLAlchemy 将使用 SMALLINT 类型，因为 MySQL 不支持布尔类型。然而，在 Python 代码中，布尔通用类型使用 bool 类型(True 或 False)来表示。\r\n",
    "\r\n",
    "The following table lists some generic column types provided by SQLAlchemy and its associated type in Python and SQL.\r\n",
    "下表列出了 SQLAlchemy 提供的一些通用列类型及其在 Python 和 SQL 中的关联类型。\r\n",
    "\r\n",
    "| SQLAlchemy   | python                | SQL          |\r\n",
    "| :----------- | :------------------ | :----------- |\r\n",
    "| `BigInteger` | `int`               | `BIGINT`     |\r\n",
    "| `Boolean`    | `bool`              | 或`SMALLINT` |\r\n",
    "| `Date`       | `datetime.date`     | `DATE`       |\r\n",
    "| `DateTime`   | `datetime.datetime` | `DATETIME`   |\r\n",
    "| `Integer`    | `int`               | `INTEGER`    |\r\n",
    "| `Float`      | `float`             | 或`REAL`     |\r\n",
    "| `Numeric`    | `decimal.Decimal`   | `NUMERIC`    |\r\n",
    "| `Text`       | `str`               | `TEXT`       |\r\n",
    "\r\n",
    "## 标准类型\r\n",
    "\r\n",
    "The types defined in this category comes directly from the SQL Standard. Very small number of database backends support these types. Unlike generic types, SQL Standard Types are not guaranteed to work in all database backends.\r\n",
    "\r\n",
    "此类别中定义的类型直接来自 SQL 标准。只有极少数的数据库后端支持这些类型。与通用类型不同，SQL 标准类型不能保证在所有数据库后端都能工作。\r\n",
    "\r\n",
    "Just like generic types, you can access these types either from sqlalchemy.types or sqlalchemy package. However, to differentiate them from generic types, names of Standard types are written in all uppercase letters. For example, SQL Standard defines a column of type Array. But currently, only PostgreSQL support this type.\r\n",
    "\r\n",
    "就像通用类型一样，您可以从 sqlalchemy.types 或 sqlalchemy 包访问这些类型。但是，为了将它们与通用类型区分开来，标准类型的名称都是用 `大写字母` 书写的。例如，SQL 标准定义了一个 Array 类型的列。但是目前，只有 PostgreSQL 支持这种类型。\r\n",
    "\r\n",
    "## Vendor Specific Types   供应商特定类型\r\n",
    "\r\n",
    "This category defines types that are specific to the database backend. We can access vendor-specific types from sqlalchemy.dialects package. For example, PostgreSQL provides an INET type to store network addresses. To use it we first have to import it from sqlalchemy.dialects package.\r\n",
    "\r\n",
    "此类别定义特定于数据库后端的类型。我们可以从 sqlalalchemy.方言包访问特定于供应商的类型。例如，PostgreSQL 提供了一种 INET 类型来存储网络地址。要使用它，我们首先必须从 sqlalchemy.tarypackage 中导入它。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# SQL标准类型 SQL标准类型定义了一个 Array 类型的列。但是目前，只有 PostgreSQL 支持这种类型。\r\n",
    "from sqlalchemy import ARRAY\r\n",
    "\r\n",
    "employee = Table('employees', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('workday', ARRAY(Integer)),\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "# Vendor Specific Types   供应商特定类型\r\n",
    "from sqlalchemy.dialects import postgresql\r\n",
    "\r\n",
    "comments = Table('comments', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('ipaddress', postgresql.INET),\r\n",
    ")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Defining Relationships 定义关系\r\n",
    "\r\n",
    "Database tables rarely exist on their own. Most of the time they are connected with one or more tables through various relationships. Primarily, there are three types of relationship that can exist between tables:\r\n",
    "\r\n",
    "数据库表很少单独存在。。大多数时候，它们通过各种关系与一个或多个表相连。主要的，表之间可以存在三种类型的关系:\r\n",
    "\r\n",
    "One-to-One relationship 一对一的关系\r\n",
    "\r\n",
    "One-to-Many relationship 一对多关系\r\n",
    "\r\n",
    "Many-to-Many relationship 多对多关系\r\n",
    "\r\n",
    "Let's see how we can define these relationships in SQLAlchemy.\r\n",
    "让我们看看如何在 SQLAlchemy 中定义这些关系。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# One-to-Many Relationship 一对多关系\r\n",
    "\r\n",
    "Two tables are related via a one-to-many relationship if a row in the first table is related to one or more rows in the second table. In the figure below, a one-to-many relationship exists between the users table and the posts table.\r\n",
    "\r\n",
    "如果第一个表中的一行与第二个表中的一行或多行相关，则两个表通过一对多关系相关。在下面的图中，用户表和 posts 表之间存在一对多的关系。\r\n",
    "\r\n",
    "![img](one-to-many-relationship-80864b2f-a886-4f21-a00c-e4b656acc3f1.png)\r\n",
    "\r\n",
    "\r\n",
    "To create a one-to-many relationship pass a ForeignKey object containing the name of the referenced column, to the Column constructor function.\r\n",
    "\r\n",
    "若要创建一对多关系，请将包含被引用列名称的 ForeignKey 对象传递给 Column 构造函数。\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric, CheckConstraint\r\n",
    "\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "user = Table('users', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('user', String(200), nullable=False),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "posts = Table('posts', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('post_title', String(200), nullable=False),\r\n",
    "    Column('post_slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),\r\n",
    "    Column('user_id', ForeignKey(\"users.id\")),\r\n",
    ")\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "In the above code, we are defining a foreign key on the user_id column of the posts table. It means that the user_id column can only contain values from the id column of the users table.\r\n",
    "\r\n",
    "在上面的代码中，我们在 posts 表的 user_id 列上定义了一个外键。这意味着 user _ id 列只能包含来自 users 表的 id 列的值。\r\n",
    "\r\n",
    "Instead of passing column name as string we could also pass the Column object directly to the ForeignKey constructor. For example:\r\n",
    "\r\n",
    "我们还可以直接将 Column 对象传递给 ForeignKey 构造函数，而不是将列名称作为字符串传递。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "user = Table('users', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('user', String(200), nullable=False),\r\n",
    ")\r\n",
    "\r\n",
    "posts = Table('posts', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('post_title', String(200), nullable=False),\r\n",
    "    Column('post_slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),\r\n",
    "    Column('user_id', Integer(), ForeignKey(user.c.id)),\r\n",
    ")\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The user.c.id refers to the id column of the users table. In doing so, remember that the definition of the referenced column (user.c.id) must come before the referencing column (posts.c.user_id).\r\n",
    "\r\n",
    "C.id 引用用户表的 id 列。在这样做时，请记住，引用列(user.c.id)的定义必须放在引用列(posts.c.user _ id)之前。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# One-to-One Relationship 一对一的关系\r\n",
    "\r\n",
    "Two tables are related via one-to-one relationship if a row in the first table is related to only one row in the second table. In the figure below, a one-to-one relationship exists between the employees table and the employee_details table. The employees table contains employees records which are public whereas the employee_details contains records which are private.\r\n",
    "\r\n",
    "如果第一个表中的一行仅与第二个表中的一行相关，则两个表通过一对一关系相关。在下面的图中，employees 表和 employee_details 表之间存在一对一的关系。employees 表包含公开的雇员记录，而 employee_details 包含私有的记录。\r\n",
    "\r\n",
    "![img](one-to-one-relationship-e94aa2ae-27f1-40c3-8192-6c7147754039.png)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "\r\n",
    "employees = Table('employees', metadata,\r\n",
    "    Column('employee_id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(200), nullable=False),\r\n",
    "    Column('last_name', String(200), nullable=False),\r\n",
    "    Column('dob', DateTime(), nullable=False),\r\n",
    "    Column('designation', String(200), nullable=False),\r\n",
    ")\r\n",
    "\r\n",
    "employee_details = Table('employee_details', metadata,\r\n",
    "    Column('employee_id', ForeignKey('employees.employee_id'), primary_key=True, ),\r\n",
    "    Column('ssn', String(200), nullable=False),\r\n",
    "    Column('salary', String(200), nullable=False),\r\n",
    "    Column('blood_group', String(200), nullable=False),\r\n",
    "    Column('residential_address', String(200), nullable=False),    \r\n",
    ")\r\n",
    "\r\n",
    "# To establish a one-to-one relationship, we define a primary key and foreign key on the same column in the employee_details table.\r\n",
    "# 为了建立一对一的关系，我们在11行 employee_details 表的同一列上定义一个主键和外键。"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Many-to-Many Relationship 多对多关系\r\n",
    "\r\n",
    "Two tables are related via a many-to-many relationship if a row in the first table is related to one or more row in the second table. In addition to that, a row in the second table can be related to one or more table in the first table. To define a many-to-many relationship we use an association table. In the figure below, a many-to-many relationship exists between the posts and the tags table.\r\n",
    "\r\n",
    "如果第一个表中的一行与第二个表中的一行或多行相关，则两个表通过多对多关系相关。除此之外，第二个表中的一行可以与第一个表中的一个或多个表相关联。要定义多对多关系，我们使用关联表。在下面的图中，posts 和标记表之间存在多对多关系。\r\n",
    "\r\n",
    "![img](many-to-many-relationship-f400a156-a019-4262-b442-c2028f30c479.png)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Many-to-Many Relationship\r\n",
    "\r\n",
    "posts = Table('posts', metadata,    \r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('post_title', String(200), nullable=False),\r\n",
    "    Column('post_slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),    \r\n",
    "    extend_existing=True,\r\n",
    ")\r\n",
    "\r\n",
    "tags = Table('tags', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('tag', String(200), nullable=False),\r\n",
    "    Column('tag_slug', String(200),  nullable=False),    \r\n",
    ")\r\n",
    "\r\n",
    "post_tags = Table('post_tags', metadata,\r\n",
    "    Column('post_id', ForeignKey('posts.id')),\r\n",
    "    Column('tag_id', ForeignKey('tags.id'))\r\n",
    ")\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "You might have noticed that the process of defining the relationship is almost identical to the way we define a relationship in SQL. This is because we using SQLAlchemy Core and the Core lets you do things in the same way as you would do in SQL.\r\n",
    "\r\n",
    "您可能已经注意到，定义关系的过程与我们在 SQL 中定义关系的方式几乎完全相同。这是因为我们使用 SQLAlchemy Core 和 Core 可以让您以与 SQL 相同的方式执行操作。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Defining Constraint at the Table level 在表级定义约束\r\n",
    "In the preceding sections, we have seen how to add constraints and indexes to a column by passing additional arguments to the Column constructor function. It turns out that just as in SQL, we can define constraints and index at the table level. The following table lists some common constraints and the name of the class to create them:\r\n",
    "\r\n",
    "在前面的部分中，我们已经了解了如何通过向 Column 构造函数传递额外的参数来向列添加约束和索引。事实证明，就像在 SQL 中一样，我们可以在表级定义约束和索引。下表列出了一些常见的约束以及创建这些约束的类名:\r\n",
    "\r\n",
    "| Constraint/Indexes 约束/索引     | Class Name 类别名称    |\r\n",
    "| :------------------------------- | :--------------------- |\r\n",
    "| Primary Key Constraint 主键约束  | `PrimaryKeyConstraint` |\r\n",
    "| Foreign Key Constraint 外键约束  | `ForeignKeyConstraint` |\r\n",
    "| Unique Constraint 独特的约束     | `UniqueConstraint`     |\r\n",
    "| Check Constraint 检查 Constraint | `CheckConstraint`      |\r\n",
    "| Index 索引                       | `Index`                |\r\n",
    "\r\n",
    "We can access these classes either from sqlalchemy.schema or sqlalchemy package. Here are some examples of how to use them:\r\n",
    "\r\n",
    "我们可以通过 sqlalchemy.schema 或 sqlalchemy 包访问这些类:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Adding Primary Key Constraint using PrimaryKeyConstraint \r\n",
    "\r\n",
    "使用 PrimaryKeyConstraint 添加主键约束\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "from sqlalchemy import PrimaryKeyConstraint\r\n",
    "\r\n",
    "parent1 = Table('parent1', metadata,\r\n",
    "    Column('acc_no', Integer()),\r\n",
    "    Column('acc_type', Integer(), nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    PrimaryKeyConstraint('acc_no', name='acc_no_pk'),\r\n",
    "    extend_existing=True,\r\n",
    ")\r\n",
    "# Here we are creating a primary key on the acc_no column. This above code is equivalent to the following:\r\n",
    "# 这里我们在 acc _ no 列上创建一个主键:\r\n",
    "\r\n",
    "\r\n",
    "parent2 = Table('parent2', metadata,\r\n",
    "    Column('acc_no', Integer(), primary_key=True),\r\n",
    "    Column('acc_type', Integer(), nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),   \r\n",
    "    extend_existing=True,\r\n",
    ")\r\n",
    "\r\n",
    "#  Mainly, the PrimaryKeyConstraint is used to define a composite primary key (a primary key spanning more than one columns). For example:\r\n",
    "# 主要地，PrimaryKeyConstraint 用于定义一个复合主键(一个跨越多列的主键)。例如:\r\n",
    "\r\n",
    "parent3 = Table('parent3', metadata,\r\n",
    "    Column('acc_no', Integer, nullable=False),\r\n",
    "    Column('acc_type', Integer, nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    PrimaryKeyConstraint('acc_no', 'acc_type', name='uniq_1'),\r\n",
    "    extend_existing=True,\r\n",
    ")\r\n",
    "\r\n",
    "# This code is equivalent to the following: 该代码相当于以下代码:\r\n",
    "\r\n",
    "parent4 = Table('parent4', metadata,\r\n",
    "    Column('acc_no', Integer, nullable=False, primary_key=True),\r\n",
    "    Column('acc_type', Integer, nullable=False, primary_key=True),\r\n",
    "    Column('name', String(16), nullable=False),   \r\n",
    "    extend_existing=True,\r\n",
    ")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating Foreign Key using ForeignKeyConstraint \r\n",
    "\r\n",
    "## 使用 ForeignKeyConstraint 创建外键\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "\r\n",
    "from sqlalchemy import ForeignKeyConstraint\r\n",
    "\r\n",
    "parent = Table('parent', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "child = Table('child', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('parent_id', Integer, nullable=False),\r\n",
    "    Column('name', String(40), nullable=False),\r\n",
    "    ForeignKeyConstraint(['parent_id'],['parent.id']),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "# Here we are creating a Foreign Key on the parent_id column which references id column of the parent table. \r\n",
    "# The above code is equivalent to the following:\r\n",
    "# 这里我们在父表的 parent_id 列上创建一个外键，该列引用父表的 parent_id 列。上述代码相当于下列代码:\r\n",
    "\r\n",
    "\r\n",
    "parent1 = Table('parent1', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "child1 = Table('child1', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('parent_id', ForeignKey('parent1.id'), nullable=False),\r\n",
    "    Column('name', String(40), nullable=False),   \r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "# The real utility of ForeignKeyConstraint comes into play when you want to define composite foreign key (a foreign key which consists of more than one columns). For example:\r\n",
    "# 当您希望定义组合外键(由多个列组成的外键)时，ForeignKeyConstraint 的真正实用程序将发挥作用。例如:\r\n",
    "\r\n",
    "\r\n",
    "parent2 = Table('parent2', metadata,\r\n",
    "    Column('id', Integer, nullable=False),\r\n",
    "    Column('ssn', Integer, nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),    \r\n",
    "    PrimaryKeyConstraint('id', 'ssn', name='uniq_1'),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "\r\n",
    "child2 = Table('child2', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('name', String(40), nullable=False),\r\n",
    "    Column('parent_id', Integer, nullable=False),\r\n",
    "    Column('parent_ssn', Integer, nullable=False),\r\n",
    "    ForeignKeyConstraint(['parent_id','parent_ssn'],['parent2.id', 'parent2.ssn']),\r\n",
    "    extend_existing=True\r\n",
    ")\r\n",
    "# Note that passing ForeignKey object to individual columns would not create a composite foreign key, instead, \r\n",
    "# it would create separate foreign keys.\r\n",
    "# 注意，将 ForeignKey 对象传递给单个列不会创建复合外键，而是创建单独的外键。\r\n"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "E:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\sql\\schema.py:3713: SAWarning: Table 'parent2' specifies columns 'acc_no' as primary_key=True, not matching locally specified columns 'id', 'ssn'; setting the current primary key columns to 'id', 'ssn'. This warning may become an exception in a future release\n",
      "  util.warn(\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating Unique Constraint using UniqueConstraint 使用 UniqueConstraint 创建唯一约束"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "# Creating Unique Constraint using UniqueConstraint\r\n",
    "from sqlalchemy import UniqueConstraint\r\n",
    "\r\n",
    "parent9 = Table('parent9', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('ssn', Integer, nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    UniqueConstraint('ssn', name='unique_ssn')\r\n",
    ")\r\n",
    "\r\n",
    "# Here we are defining unique constraint on the ssn column. \r\n",
    "# The optional name keyword argument is used to provide a name to the unique constraint. \r\n",
    "# The above code is equivalent to the following:\r\n",
    "# 这里我们在 ssn 列上定义唯一约束。可选的 name 关键字参数用于为唯一约束提供名称。上述代码相当于下列代码:\r\n",
    "\r\n",
    "parent8 = Table('parent8', metadata,\r\n",
    "    Column('id', Integer, primary_key=True),\r\n",
    "    Column('ssn', Integer, unique=True, nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),    \r\n",
    ")\r\n",
    "\r\n",
    "# The UniqueConstraint is commonly used to define unique constraint containing multiple columns. For example:\r\n",
    "# UniqueConstraint 通常用于定义包含多个列的唯一约束。例如:\r\n",
    "\r\n",
    "parent7 = Table('parent7', metadata,\r\n",
    "    Column('acc_no', Integer, primary_key=True),\r\n",
    "    Column('acc_type', Integer, nullable=False),\r\n",
    "    Column('name', String(16), nullable=False),\r\n",
    "    UniqueConstraint('acc_no', 'acc_type', name='uniq_1')\r\n",
    ")\r\n",
    "\r\n",
    "# Here, I define a unique constraint on acc_no and acc_type, as a result, acc_no and acc_type, when taken together must be unique.\r\n",
    "# 在这里，我对 acc_no 和 acc_type 定义了一个唯一的约束，因此，将 acc_no 和 acc_type 放在一起时必须是唯一的。\r\n",
    "\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating Check Constraint with CheckConstraint 使用 CheckConstraint 创建检查约束\r\n",
    "\r\n",
    "A CHECK constraint allows us to define a condition that will be evaluated while inserting or updating data. If the condition evaluates to true then the data is saved into the database. Otherwise, an error is raised.\r\n",
    "\r\n",
    "CHECK 约束允许我们定义将在插入或更新数据时计算的条件。如果条件计算结果为 true，则数据将保存到数据库中。否则，将引发错误。\r\n",
    "\r\n",
    "We can add CHECK constraint using the CheckConstraint construct.\r\n",
    "\r\n",
    "我们可以使用 CheckConstraint 构造添加 CHECK 约束。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "\r\n",
    "from sqlalchemy import CheckConstraint\r\n",
    "\r\n",
    "employee = Table('employee', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('name', String(100), nullable=False),\r\n",
    "    Column('salary', Integer(), nullable=False),\r\n",
    "    CheckConstraint('salary < 100000', name='salary_check')\r\n",
    ")\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating indexes using Index  使用 Index 创建索引\r\n",
    "\r\n",
    "The index keyword argument which we learned in earlier in this lesson allows us to add index on per column basis. \r\n",
    "\r\n",
    "Another way to define index is to use the Index construct. For example:\r\n",
    "\r\n",
    "我们在本课前面学到的 index 关键字参数允许我们在每列基础上添加索引。定义索引的另一种方法是使用 Index 构造。例如:\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "\r\n",
    "from sqlalchemy import Index\r\n",
    "\r\n",
    "a_table1 = Table('a_table1', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(100), nullable=False),\r\n",
    "    Column('middle_name', String(100)),\r\n",
    "    Column('last_name', String(100), nullable=False),\r\n",
    "    Index('idx_col1', 'first_name')  \r\n",
    ")\r\n",
    "\r\n",
    "# Here we are creating an index on the first_name column. This code is equivalent to the following:\r\n",
    "# 这里我们在名字列上创建一个索引:\r\n",
    "a_table2 = Table('a_table2', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(100), nullable=False, index=True),\r\n",
    "    Column('middle_name', String(100)),\r\n",
    "    Column('last_name', String(100), nullable=False),    \r\n",
    ")\r\n",
    "\r\n",
    "# If your queries involved searching through a particular set of fields \r\n",
    "# then you might get a performance boost by creating a composite index (i.e. an index on multiple columns), \r\n",
    "# which is the primary purpose of Index. Here is an example:\r\n",
    "# 如果您的查询涉及到通过一组特定的字段进行搜索，那么您可以通过创建一个复合索引(即多列上的一个索引)来提高性能，\r\n",
    "# 这是 Index 的主要目的。下面是一个例子:\r\n",
    "\r\n",
    "a_table3 = Table('a_table3', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(100), nullable=False),\r\n",
    "    Column('middle_name', String(100)),\r\n",
    "    Column('last_name', String(100), nullable=False),\r\n",
    "    Index('idx_col1', 'first_name', 'last_name')  \r\n",
    ")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Accessing Tables and Columns from MetaData 从元数据访问表和列\r\n",
    "Recall that the MetaData object holds all the information about the database and the tables it contains. \r\n",
    "We can access the table objects, using the following two attributes.\r\n",
    "\r\n",
    "回想一下，MetaData 对象保存有关数据库及其包含的表的所有信息。我们可以使用以下两个属性来访问表对象。\r\n",
    "\r\n",
    "下面的脚本展示了这两个属性的作用:\r\n",
    "\r\n",
    "| Attribute       | Description                                                  |\r\n",
    "| :-------------- | :----------------------------------------------------------- |\r\n",
    "| `tables`        | returns a dictionary-type object called `immutabledict`  with table name as key and the corresponding `Table` object as value. 返回一个名为 immutabledict 的不可变的字典类型对象，表名作为键，对应的 Table 对象作为值。|\r\n",
    "| `sorted_tables` | returns a list of `Table` object sorted in order of foreign key dependency. In other words, the tables which have dependencies is placed first before the actual dependencies. For example, if `posts` table has a foreign key which refers to the `id` column of the `users` table, then the `users` table is placed first followed by the `posts` table. 返回按外键依赖关系排序的 Table 对象列表。 换句话说，具有依赖关系的表首先放置在实际依赖关系之前。 例如，如果posts 表有一个引用users 表的id 列的外键，那么首先放置users 表，然后放置posts 表。 |"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, Column, Text, DateTime, Boolean, ForeignKey\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "user = Table('users', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('user', String(200), nullable=False),\r\n",
    ")\r\n",
    "\r\n",
    "posts = Table('posts', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('post_title', String(200), nullable=False),\r\n",
    "    Column('post_slug', String(200),  nullable=False),\r\n",
    "    Column('content', Text(),  nullable=False),\r\n",
    "    Column('user_id', Integer(), ForeignKey(\"users.id\")),\r\n",
    ")\r\n",
    "\r\n",
    "for t in metadata.tables:\r\n",
    "    print(metadata.tables[t])\r\n",
    "\r\n",
    "print('-------------')  \r\n",
    "\r\n",
    "for t in metadata.sorted_tables:\r\n",
    "    print(t.name) # print table name\r\n",
    "\r\n",
    "# Expected Output: 预期产出:\r\n",
    "# posts\r\n",
    "# users\r\n",
    "# -------------\r\n",
    "# users\r\n",
    "# posts"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 访问列\r\n",
    "\r\n",
    "Once we have access to the Table instance we can can easily access any relevant detail about the columns as follows:\r\n",
    "\r\n",
    "一旦我们能够访问 Table 实例，我们就可以轻松地访问关于列的任何相关细节，如下所示:\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "\r\n",
    "\r\n",
    "print(posts.columns)         # return a list of columns\r\n",
    "print(posts.c)               # same as posts.columns\r\n",
    "print(posts.foreign_keys)    # returns a set containing foreign keys on the table\r\n",
    "print(posts.primary_key)     # returns the primary key of the table\r\n",
    "print(posts.metadata)        # get the MetaData object from the table\r\n",
    "print(posts.columns.post_title.name)     # returns the name of the column\r\n",
    "print(posts.columns.post_title.type)     # returns the type of the column\r\n",
    "\r\n",
    "# Expected Output: 预期产出:\r\n",
    "# > ['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']\r\n",
    "# > ['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']\r\n",
    "# > {ForeignKey('users.id')}\r\n",
    "# > PrimaryKeyConstraint(Column('id', Integer(), table=<posts>, primary_key=True, nullable=False))\r\n",
    "# > MetaData(bind=None)\r\n",
    "# > post_title\r\n",
    "# > VARCHAR(200)\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Creating Tables\r\n",
    "\r\n",
    "To create the tables stored in the MetaData instance call MetaData.create_all() method with the Engine object.\r\n",
    "\r\n",
    "要创建存储在元数据实例中的表，可以使用 Engine 对象调用 MetaData.create_all()方法。\r\n",
    "\r\n",
    "```python\r\n",
    "metadata.create_all(engine)\r\n",
    "```\r\n",
    "\r\n",
    "The create_all() method only creates table if it doesn't already exist in the database. That means you can call create_all() safely multiple times. Note that calling create_all() method after changing the table definition will not alter the table schema. To do that, we can use a database migration tool called Alembic. We will learn more about it in the future lesson.\r\n",
    "\r\n",
    "create_all ()方法只在数据库中不存在表的情况下创建表。这意味着您可以多次安全地调用 create_all()。请注意，在更改表定义后调用 create_all()方法不会更改表模式。为此，我们可以使用一个名为 Alembic 的数据库迁移工具。我们将在今后的课程中学到更多关于它的知识。\r\n",
    "\r\n",
    "We can also drop all the tables in the database using MetaData.drop_all() method.\r\n",
    "\r\n",
    "我们还可以使用 MetaData.drop _ all ()方法删除数据库中的所有表。\r\n",
    "\r\n",
    "In this tutorial we will be working with the database of an e-commerce application. The database consists of following 4 tables:\r\n",
    "\r\n",
    "在本教程中，我们将使用电子商务应用程序的数据库。该数据库由以下4个表组成:\r\n",
    "\r\n",
    "### customers table stores all the information about the customer. It consists of following columns:\r\n",
    "### Customers 表存储有关客户的所有信息。它由以下列组成:\r\n",
    "\r\n",
    "\r\n",
    "id - primary key\r\n",
    "first_name - first name of customer\r\n",
    "last_name - last name of customer\r\n",
    "username - a unique username\r\n",
    "email - a unique email\r\n",
    "address - customer address\r\n",
    "town - customer town name\r\n",
    "created_on - date and time of account creation\r\n",
    "updated_on - date and time the account was last updated\r\n",
    "\r\n",
    "### items table stores information about products. It consists of following columns:\r\n",
    "### Items 表存储有关产品的信息。它由以下列组成:\r\n",
    "\r\n",
    "id - primary key\r\n",
    "name - item name\r\n",
    "cost_price - cost price of item\r\n",
    "selling_price - selling price of item\r\n",
    "quantity - quantity of item in the stock\r\n",
    "\r\n",
    "### orders stores information about the orders made by the customers. It consists of following columns:\r\n",
    "### “订单”存储有关客户订单的信息，包括以下栏目:\r\n",
    "\r\n",
    "id - primary key\r\n",
    "customer_id - foreign key to id column in the customers table\r\n",
    "date_placed - date and time the order was placed\r\n",
    "date_shipped - date and time the order was shipped\r\n",
    "### order_lines stores details of items in the each order. It consists of following columns:\r\n",
    "### Order_lines 在每个订单中存储项目的详细信息，它由以下列组成:\r\n",
    "\r\n",
    "id - primary key\r\n",
    "order_id - foreign key to id column in the orders table\r\n",
    "item_id - foreign key to id column in the items table\r\n",
    "quantity - quantity of item ordered\r\n",
    "The following is an ER diagram of the database."
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The following is an ER diagram of the database.下面是数据库的 ER 图表。\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
    "![img](e-commerce-database-er-diagram.png)\r\n",
    "\r\n",
    "Here is the complete code to create these tables. 下面是创建这些表的完整代码。\r\n",
    "\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric, CheckConstraint\r\n",
    "\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/sqlalchemy_tuts\")\r\n",
    "\r\n",
    "customers = Table('customers', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(100), nullable=False),\r\n",
    "    Column('last_name', String(100), nullable=False),\r\n",
    "    Column('username', String(50), nullable=False),\r\n",
    "    Column('email', String(200), nullable=False),\r\n",
    "    Column('address', String(200), nullable=False),\r\n",
    "    Column('town', String(50), nullable=False),\r\n",
    "    Column('created_on', DateTime(), default=datetime.now),\r\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "items = Table('items', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('name', String(200), nullable=False),\r\n",
    "    Column('cost_price', Numeric(10, 2), nullable=False),\r\n",
    "    Column('selling_price', Numeric(10, 2),  nullable=False),\r\n",
    "    Column('quantity', Integer(), nullable=False),\r\n",
    "    CheckConstraint('quantity > 0', name='quantity_check')\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "orders = Table('orders', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('customer_id', ForeignKey('customers.id')),\r\n",
    "    Column('date_placed', DateTime(), default=datetime.now),\r\n",
    "    Column('date_shipped', DateTime())\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "order_lines = Table('order_lines', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('order_id', ForeignKey('orders.id')),\r\n",
    "    Column('item_id', ForeignKey('items.id')),\r\n",
    "    Column('quantity', Integer())\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "metadata.create_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "print(posts.columns)         # return a list of columns\r\n",
    "print(posts.c)               # same as posts.columns\r\n",
    "print(posts.foreign_keys)    # returns a set containing foreign keys on the table\r\n",
    "print(posts.primary_key)     # returns the primary key of the column\r\n",
    "print(posts.metadata)        # get the MetaData object from the table\r\n",
    "print(posts.columns.post_title.name)     # returns the name of the column\r\n",
    "print(posts.columns.post_title.type)     # returns the type of the column"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "In the next lesson, we will learn how to perform CRUD operations on the database using SQL Expression Language.\r\n",
    "\r\n",
    "在下一课中，我们将学习如何使用 SQL 表达式语言对数据库执行 CRUD 操作。\r\n",
    "\r\n",
    "[粗翻]  2021-08-24 Fully jupyter notebook and Chinese Verion CRAFTED BY YULK "
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}